﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class MyNoteService
    {
        public List<MyNote> GetAllNote()
        {
            List<MyNote> list = new List<MyNote>();
            string sql = "select * from MyNote";
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MyNote note = new MyNote();
                    note.NoteId = (int)dr["NoteId"];
                    note.NoteTitle = dr["NoteTitle"].ToString();
                    note.NoteContent = dr["NoteContent"].ToString();
                    note.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    note.CreateUser = dr["CreateUser"].ToString();
                    list.Add(note);
                }
            }
            return list;
        }
        public MyNote GetNoteById(int id)
        {
            MyNote note = new MyNote();
            string sql = string.Format("select * from MyNote where NoteId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {

                    note.NoteId = (int)dr["NoteId"];
                    note.NoteTitle = dr["NoteTitle"].ToString();
                    note.NoteContent = dr["NoteContent"].ToString();
                    note.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    note.CreateUser = dr["CreateUser"].ToString();

                }
            }
            return note;
        }
        public void AddNote(MyNote note)
        {
            string sql = "insert [MyNote] (NoteTitle,NoteContent,CreateTime,CreateUser) values(@NoteTitle,@NoteContent,@CreateTime,@CreateUser)";
            SqlParameter[] spa = {
                                    new SqlParameter("@NoteTitle",note.NoteTitle),
                                    new SqlParameter("@NoteContent",note.NoteContent),
                                    new SqlParameter("@CreateTime",note.CreateTime),
                                    new SqlParameter("@CreateUser",note.CreateUser)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void UpdateNote(MyNote note)
        {
            string sql = "update [MyNote] set NoteTitle=@NoteTitle,NoteContent=@NoteContent,CreateTime=@CreateTime,CreateUser=@CreateUser where @NoteId=@NoteId";
            SqlParameter[] spa = {
                                    new SqlParameter("@NoteId",note.NoteId),
                                    new SqlParameter("@NoteTitle",note.NoteTitle),
                                    new SqlParameter("@NoteContent",note.NoteContent),
                                    new SqlParameter("@CreateTime",note.CreateTime),
                                    new SqlParameter("@CreateUser",note.CreateUser)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void DeleteNote(int id)
        {
            string sql = string.Format("delete [MyNote] where NoteId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
    }
}
